package com.company;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.concurrent.atomic.AtomicBoolean;
import java.util.concurrent.atomic.AtomicLong;

public class SqliteTools {
    private static SqliteHelper h;
    private static final String dbName = "obr.db";
    private static SimpleDateFormat sf = new SimpleDateFormat("yyyy/MM/dd");

    private static void initHelper() {
        if (h == null) {
            try {
                h = new SqliteHelper(dbName);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void init() {
        try {
            if (h == null) initHelper();
            h.executeUpdate("create table if not exists sel(type varchar(20) not null," +
                    "time date not null," +
                    "code varchar(200)," +
                    "status varchar(5) not null," +
                    "bc varchar(200)," +
                    "bz varchar(200)," +
                    "czy varchar(200)," +
                    "czybh varchar(200)" +
                    ");");
            h.executeUpdate("create table if not exists g(s1 varchar(100)," +
                    "s2 varchar(100)," +
                    "s3 varchar(100)," +
                    "s4 varchar(100)" +
                    ");");
            h.executeUpdate("create table if not exists p(s1 varchar(100)," +
                    "s2 varchar(100)," +
                    "s3 varchar(100)," +
                    "s4 varchar(100)," +
                    "s5 varchar(100)," +
                    "s6 varchar(100)," +
                    "s7 varchar(100)," +
                    "s8 varchar(100)," +
                    "s9 varchar(100)," +
                    "s10 varchar(100)" +
                    ");");
            Calendar now = Calendar.getInstance();
            now.add(Calendar.DAY_OF_MONTH, -30);
            String endDate = sf.format(now.getTime());
            h.executeUpdate("delete from sel where time < '" + endDate + "'");//删除60天之前的数据
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    static List<String> inserts = new ArrayList<>();
    static AtomicLong startTime = new AtomicLong(0);
    static AtomicBoolean own = new AtomicBoolean(false);

    public static void insertData(boolean isOwn, String type, String code, String status, String bc, String bz, String czy, String czybh) {
        if (h == null) initHelper();
        try {
            if (startTime.longValue() == 0) {
                startTime.set(System.currentTimeMillis());
            }
            String time = sf.format(new Date());
            String insert = "insert into sel values('" + type + "'," +
                    "'" + time + "'," +
                    "'" + code + "'," +
                    "'" + status + "'," +
                    "'" + bc + "'," +
                    "'" + bz + "'," +
                    "'" + czy + "'," +
                    "'" + czybh + "'" +
                    ")";
            synchronized (inserts) {
                if (!isOwn)
                    inserts.add(insert);
                if (System.currentTimeMillis() - startTime.longValue() > 10000) {
                    startTime.set(0);
                    h.executeLargeUpdate(inserts);
                    inserts.clear();
                }
                if (inserts.size() != 0 && !own.get()) {
                    new Thread() {
                        @Override
                        public void run() {
                            try {
                                own.set(true);
                                sleep(10000);
                                insertData(true, "", "", "", "", "", "", "");
                                own.set(false);
                            } catch (InterruptedException e) {
                                e.printStackTrace();
                            }
                        }
                    }.start();
                }
            }
//            synchronized (h) {
//                h.executeUpdate(insert);
//            }
//            System.out.println(insert);
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static List<GUser> queryData() {
        if (h == null) initHelper();
        String select = "select * from g";
        synchronized (h) {
            try {
                List<GUser> users = h.executeQuery(select, new RowMapper<GUser>() {
                    @Override
                    public GUser mapRow(ResultSet rs, int index) throws SQLException {
                        GUser user = new GUser();
                        user.setS1(rs.getString("s1"));
                        user.setS2(rs.getString("s2"));
                        user.setS3(rs.getString("s3"));
                        user.setS4(rs.getString("s4"));
                        return user;
                    }
                });
                return users;
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
        }
        return new ArrayList<>();
    }

    public static List<PUser> queryPData() {
        if (h == null) initHelper();
        String select = "select * from p";
        synchronized (h) {
            try {
                List<PUser> users = h.executeQuery(select, new RowMapper<PUser>() {
                    @Override
                    public PUser mapRow(ResultSet rs, int index) throws SQLException {
                        PUser user = new PUser();
                        user.setS1(rs.getString("s1"));
                        user.setS2(rs.getString("s2"));
                        user.setS3(rs.getString("s3"));
                        user.setS4(rs.getString("s4"));
                        user.setS5(rs.getString("s5"));
                        user.setS6(rs.getString("s6"));
                        user.setS7(rs.getString("s7"));
                        user.setS8(rs.getString("s8"));
                        user.setS9(rs.getString("s9"));
                        user.setS10(rs.getString("s10"));
                        return user;
                    }
                });
                return users;
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
        }
        return new ArrayList<>();
    }

    public static void insertGUser(String s1, String s2, String s3, String s4) {
        String insert = "insert into g values('" + s1 + "'," +
                "'" + s2 + "'," +
                "'" + s3 + "'," +
                "'" + s4 + "'" +
                ")";
        synchronized (h) {
            try {
                h.executeUpdate(insert);
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
        }
    }

    public static void insertPUser(String s1, String s2, String s3, String s4, String s5, String s6, String s7, String s8, String s9, String s10) {
        String insert = "insert into p values('" + s1 + "'," +
                "'" + s2 + "'," +
                "'" + s3 + "'," +
                "'" + s4 + "'," +
                "'" + s5 + "'," +
                "'" + s6 + "'," +
                "'" + s7 + "'," +
                "'" + s8 + "'," +
                "'" + s9 + "'," +
                "'" + s10 + "'" +
                ")";
        synchronized (h) {
            try {
                h.executeUpdate(insert);
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
        }
    }

    public static void deleteGuser(GUser u) {
        String delete = "delete from g where s1 = '" + u.getS1() + "'" +
                "and s2 = '" + u.getS2() + "'" +
//                "and s3 = '" + u.getS3() + "'" +
                "and s4 = '" + u.getS4() + "'";
        synchronized (h) {
            try {
                h.executeUpdate(delete);
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
        }
    }

    public static void deletePuser(PUser u) {
        String delete = "delete from p where s1 = '" + u.getS1() + "'" +
                "and s2 = '" + u.getS2() + "'" +
                "and s3 = '" + u.getS3() + "'" +
                "and s4 = '" + u.getS4() + "'" +
//                "and s5 = '" + u.getS5() + "'" +
                "and s6 = '" + u.getS6() + "'" +
                "and s7 = '" + u.getS7() + "'" +
                "and s8 = '" + u.getS8() + "'" +
                "and s9 = '" + u.getS9() + "'" +
                "and s10 = '" + u.getS10() + "'";
        synchronized (h) {
            try {
                h.executeUpdate(delete);
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
        }
    }

    public static List<Rt> queryData(String type, String code, String status, String bc, String bz, String czy, String czybh, String time1, String time2) {
        try {
            if (h == null) initHelper();
//            SqliteHelper h=new SqliteHelper(dbName);
            String select = "select * from sel where 1=1 ";
            if (!status.equals("全部")) {
                select += " and status = '" + status + "'";
            }
            if (!TextUtils.isEmpty(type)) {
                select += " and type = '" + type + "'";
            }
            if (!TextUtils.isEmpty(code)) {
                select += " and code like '%" + code + "%'";
            }
            if (!TextUtils.isEmpty(bc)) {
                select += " and bc like '%" + bc + "%'";
            }
            if (!TextUtils.isEmpty(bz)) {
                select += " and bz like '%" + bz + "%'";
            }
            if (!TextUtils.isEmpty(czy)) {
                select += " and czy like '%" + czy + "%'";
            }
            if (!TextUtils.isEmpty(czybh)) {
                select += " and czybh like '%" + czybh + "%'";
            }
            if (!TextUtils.isEmpty(time1)) {
                select += " and time >= '" + time1 + "'";
            }
            if (!TextUtils.isEmpty(time2)) {
                select += " and time <= '" + time2 + "'";
            }
            System.out.println(select);
            List<Rt> list = null;
            synchronized (h) {
                list = h.executeQuery(select, new RowMapper<Rt>() {
                    @Override
                    public Rt mapRow(ResultSet rs, int index) throws SQLException {
                        Rt rt = new Rt();
                        rt.setType(rs.getString("type"));
                        rt.setBc(rs.getString("bc"));
                        rt.setBz(rs.getString("bz"));
                        rt.setCode(rs.getString("code"));
                        rt.setCzy(rs.getString("czy"));
                        rt.setCzybh(rs.getString("czybh"));
                        rt.setTime(rs.getString("time"));
                        rt.setStatus(rs.getString("status"));
                        return rt;
                    }
                });
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return null;
    }

}
